﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ATA.Data.MisaDAL
{
    public class DAL
    {
        public string MisaConnectionString;
        public string ATAConnectionString;

        public DAL()
        {
        }

        public DAL(string misaConnectionString, string ataConnectionString)
        {
            MisaConnectionString = misaConnectionString;
            ATAConnectionString = ataConnectionString;
        }

        public List<DoanhSoBanHang> GetDoanhSoTheoDonHang(int month, int year, Guid accountingObjectId)
        {
            SqlConnection con = null;
            List<DoanhSoBanHang> result = new List<DoanhSoBanHang>();
            try
            {
                con = new SqlConnection();
                con.ConnectionString = MisaConnectionString;
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 0;
                cmd.CommandText = @"select ao.AccountingObjectCode as MaKH, 
                                                ao.AccountingObjectName as TenKH,
                                                ii.InventoryItemCode AS MaHang,
                                                ISNULL(iic.InventoryCategoryCode, '') as PhanLoaiHang, 
                                                ii.InventoryItemName as TenHang, 
		                                        ii.Unit as DonVi,   
                                                (vfp.Amount + vfp.VATAmount) as DoanhSoTrongThang,
                                                vfp.Quantity AS SoLuong, 
                                                vfp.RefNo as SoHoaDon,
		                                        SUM(ISNULL(cardc.Amount, 0)) AS TienThanhToan, 
		                                        SUM(ISNULL(cardc.ReceipableAmount, 0)) AS TienChuaThanhToan,
		                                        car.RefDate as NgayThanhToan,
                                                vfp.PostedDate as NgayHoaDon
                                        from	View_VoucherForPayable vfp
                                        INNER JOIN InventoryItem ii ON vfp.InventoryItemID = ii.InventoryItemID
                                        INNER JOIN accountingObject ao ON vfp.AccountingObjectID = ao.AccountingObjectID
                                        LEFT JOIN InventoryItemCategory iic ON  ii.InventoryCategoryID = iic.InventoryCategoryID
                                        LEFT JOIN CAReceiptDetailCustomer cardc ON vfp.RefId = cardc.SaleInvoiceRefId
                                        LEFT JOIN CAReceipt car ON cardc.RefId = car.RefId
                                        where 
                                                ao.IsCustomer = 1 and
                                                (vfp.CreditAccount LIKE '%511%' OR vfp.CreditAccount LIKE '%512%') 
                                                AND vfp.Posted=1
                                                AND (DATEPART(month, vfp.PostedDate) = @Month AND DATEPART(year, vfp.PostedDate) = @Year)
                                                AND (ao.AccountingObjectId = @AccountingObjectId OR @NoFilter = 1)
                                        GROUP BY vfp.Amount, ao.AccountingObjectCode, ao.AccountingObjectName, ii.InventoryItemID, ii.InventoryItemCode, iic.InventoryCategoryCode, ii.InventoryItemName, ii.Unit, vfp.RefNo, car.RefDate, vfp.Quantity, vfp.VATAmount, vfp.PostedDate
                                        order by TenKH, InventoryCategoryCode";

                SqlParameter parMonth = new SqlParameter("@Month", System.Data.SqlDbType.Int);
                parMonth.Value = month;
                cmd.Parameters.Add(parMonth);

                SqlParameter parYear = new SqlParameter("@Year", System.Data.SqlDbType.Int);
                parYear.Value = year;
                cmd.Parameters.Add(parYear);

                SqlParameter parAccountingObjectId = new SqlParameter("@AccountingObjectId", System.Data.SqlDbType.UniqueIdentifier);
                parAccountingObjectId.Value = accountingObjectId;
                cmd.Parameters.Add(parAccountingObjectId);

                SqlParameter parNoFilter = new SqlParameter("@NoFilter", System.Data.SqlDbType.Bit);
                parNoFilter.Value = accountingObjectId == Guid.Empty ? 1 : 0;
                cmd.Parameters.Add(parNoFilter);


                var reader = cmd.ExecuteReader();
                if (reader != null && reader.HasRows)
                    while (reader.Read())
                    {
                        var currentRow = CookDoanhSoRow(reader);
                        result.Add(currentRow);
                    }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (con != null)
                    con.Close();
            }

            return result;
        }

        public List<AccountingObject> GetListCustomer(bool voucherExists)
        {
            SqlConnection con = null;
            List<AccountingObject> result = new List<AccountingObject>();
            try
            {
                con = new SqlConnection();
                con.ConnectionString = MisaConnectionString;
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 0;
                cmd.CommandText = @"SELECT ao.AccountingObjectID
		                            , ao.AccountingObjectCode
		                            , ao.AccountingObjectName
                                    FROM dbo.AccountingObject ao
                                    WHERE ao.IsCustomer = 1 
                                    AND (ao.AccountingObjectID IN (SELECT vfp.AccountingObjectID FROM View_VoucherForPayable vfp WHERE (CreditAccount LIKE '%511%' OR CreditAccount LIKE '%512%') AND Posted=1) OR @VoucherExists=0)";

                SqlParameter parVoucherExists = new SqlParameter("@VoucherExists", System.Data.SqlDbType.Bit);
                parVoucherExists.Value = voucherExists;
                cmd.Parameters.Add(parVoucherExists);

                var reader = cmd.ExecuteReader();
                if (reader != null && reader.HasRows)
                    while (reader.Read())
                    {
                        var currentRow = CookAccountingObjectRow(reader);
                        result.Add(currentRow);
                    }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (con != null)
                    con.Close();
            }

            return result;
        }

        public List<AccountingObject> GetListCustomerByBrand(bool voucherExists, string pv_strBrandID)
        {
            SqlConnection con = null;
            List<AccountingObject> result = new List<AccountingObject>();
            try
            {
                con = new SqlConnection();
                con.ConnectionString = MisaConnectionString;
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 0;
                cmd.CommandText = @"SELECT ao.AccountingObjectID
		                            , ao.AccountingObjectCode
		                            , ao.AccountingObjectName
                                    FROM dbo.AccountingObject ao
                                    WHERE ao.IsCustomer = 1 
                                    AND ao.AccountingObjectCode like '@BranCode%'
                                    AND (ao.AccountingObjectID IN (SELECT vfp.AccountingObjectID FROM View_VoucherForPayable vfp WHERE (CreditAccount LIKE '%511%' OR CreditAccount LIKE '%512%') AND Posted=1) OR @VoucherExists=0)";

                SqlParameter parBrandCode = new SqlParameter("@BranCode", System.Data.SqlDbType.VarChar);
                parBrandCode.Value = pv_strBrandID;
                cmd.Parameters.Add(parBrandCode);

                SqlParameter parVoucherExists = new SqlParameter("@VoucherExists", System.Data.SqlDbType.Bit);
                parVoucherExists.Value = voucherExists;
                cmd.Parameters.Add(parVoucherExists);

                var reader = cmd.ExecuteReader();
                if (reader != null && reader.HasRows)
                    while (reader.Read())
                    {
                        var currentRow = CookAccountingObjectRow(reader);
                        result.Add(currentRow);
                    }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (con != null)
                    con.Close();
            }

            return result;
        }

        public List<ChietKhau> GetListChietKhau(int month, int year)
        {
            if (month == 0 || year == 0)
                return new List<ChietKhau>();

            SqlConnection con = null;
            var result = new List<ChietKhau>();
            try
            {
                con = new SqlConnection();
                con.ConnectionString = ATAConnectionString;
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 0;
                cmd.CommandText = @"select * from dbo.ChietKhau A, dbo.ChietKhauDoanhSo B, dbo.DieuKienGiamGia C, dbo.NhomSanPham D, dbo.LoaiThoiGian E
                                    where
                                    A.DieuKienGiamGiaID = C.DieuKienID
                                    and B.NhomSanPhamID = D.NhomSanPhamID
                                    and B.LoaiThoiGianID = E.LoaiThoiGianID
                                    and B.ChietKhauID = A.ChietKhauID
                                    and TenLoaiThoiGian = 'Month' and C.IsActive = 1
                                    AND @ReportDate BETWEEN StartDate AND EndDate ";

                SqlParameter parReportDate = new SqlParameter("@ReportDate", System.Data.SqlDbType.DateTime);
                parReportDate.Value = new DateTime(year, month, 1);
                cmd.Parameters.Add(parReportDate);

                var reader = cmd.ExecuteReader();
                if (reader != null && reader.HasRows)
                    while (reader.Read())
                    {
                        var currentRow = CookChietKhauRow(reader);
                        result.Add(currentRow);
                    }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (con != null)
                    con.Close();
            }

            return result;
        }

      


        #region Cook methods
        private DoanhSoBanHang CookDoanhSoRow(SqlDataReader reader)
        {
            var result = new DoanhSoBanHang();
            result.MaKH = reader["MaKH"].ToString();
            result.TenKH = reader["TenKH"].ToString();
            result.MaHang = reader["MaHang"].ToString();
            result.TenHang = reader["TenHang"].ToString();
            string category = reader["PhanLoaiHang"].ToString();
            if (category.Contains("-"))
            {
                string[] categoryParts = category.Split('-');
                if (categoryParts.Count() >= 3)
                {
                    result.PhanloaiHang = categoryParts[0];
                    result.LoaiSanPham = categoryParts[1];
                    result.ChatLuong = categoryParts[2];
                }
            }
            else
            {
                result.PhanloaiHang = "";
                result.LoaiSanPham = "";
                    result.ChatLuong = "";
            }

            result.DoanhSo = decimal.Parse(reader["DoanhSoTrongThang"].ToString());
            result.SoLuong = decimal.Parse(reader["SoLuong"].ToString());
            result.DonVi = reader["DonVi"].ToString();
            result.NgayThanhToan = reader["NgayThanhToan"].ToString() == string.Empty ? null : (DateTime?) DateTime.Parse(reader["NgayThanhToan"].ToString());
            result.NgayHoaDon = reader["NgayHoaDon"].ToString() == string.Empty ? null : (DateTime?)DateTime.Parse(reader["NgayHoaDon"].ToString());
            result.SoHoaDon = reader["SoHoaDon"].ToString();
            result.TienChuaThanhToan = decimal.Parse(reader["TienChuaThanhToan"].ToString());
            result.TienThanhToan = decimal.Parse(reader["TienThanhToan"].ToString());

            return result;
        }

        private ChietKhau CookChietKhauRow(SqlDataReader reader)
        {
            var result = new ChietKhau();
            result.DiscountPercent = decimal.Parse(reader["MucThuong"].ToString());
            result.EndDate = DateTime.Parse(reader["EndDate"].ToString());
            result.Id = new Guid(reader["ChietKhauID"].ToString());
            result.ItemName = reader["SanPhamID"].ToString();
            result.RequiredAmount = decimal.Parse(reader["DoanhSo"].ToString());
            result.StartDate = DateTime.Parse(reader["StartDate"].ToString());
            result.Name = reader["Header"].ToString();
            result.ItemType = reader["SanPhamID"].ToString();
            result.ToanTuSoSanh = reader["ToanTuSoSanh"].ToString();

            return result;
        }

        private AccountingObject CookAccountingObjectRow(SqlDataReader reader)
        {
            var result = new AccountingObject();

            result.AccountingObjectCode = reader["AccountingObjectCode"].ToString();
            result.AccountingObjectId = new Guid(reader["AccountingObjectId"].ToString());
            result.AccountingObjectName = reader["AccountingObjectName"].ToString();

            return result;
        }
        #endregion

        public DataSet GetAllActiveBranchs()
        {
            SqlConnection con = null;
            try
            {
                //con = new SqlConnection();
                //con.ConnectionString = MisaConnectionString;
                //con.Open();

                //SqlCommand cmd = new SqlCommand("SELECT BranchID, BranchCode, BranchName FROM Branch", con);

                //SqlDataAdapter da = new SqlDataAdapter(cmd);

                DataSet v_dsResults = new DataSet();

                SelectSqlSrvRows(v_dsResults, MisaConnectionString, "SELECT BranchID, BranchCode, BranchName, IsSystem FROM Branch WHERE INACTIVE = 0");

                return v_dsResults;
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public DataSet GetActiveBranchsByID(string pv_strBranchID)
        {
            SqlConnection con = null;
            try
            {
                //con = new SqlConnection();
                //con.ConnectionString = MisaConnectionString;
                //con.Open();

                //SqlCommand cmd = new SqlCommand("SELECT BranchID, BranchCode, BranchName FROM Branch", con);

                //SqlDataAdapter da = new SqlDataAdapter(cmd);

                DataSet v_dsResults = new DataSet();

                SelectSqlSrvRows(v_dsResults, MisaConnectionString, "SELECT BranchID, BranchCode, BranchName, IsSystem FROM Branch WHERE INACTIVE = 0 AND BRANCHID = '" + pv_strBranchID + "'");

                return v_dsResults;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public DataSet SelectSqlSrvRows(DataSet dataset, string connection, string query)
        {
            SqlConnection conn = new SqlConnection(connection);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand(query, conn);
            adapter.Fill(dataset);
            return dataset;
        }


        public bool ValidateUser(string pv_strUsername, string pv_strPassword, string pv_strBranchID)
        {
            SqlConnection con = null;
            try
            {
                con = new SqlConnection();
                con.ConnectionString = MisaConnectionString;
                con.Open();

                SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM MSC_USER WHERE INACTIVE = 0 AND UserName = '" + pv_strUsername + 
                                        "' AND Password = '" + pv_strPassword + "'" +
                                        " AND (BranchID = '" + pv_strBranchID + "' OR IsSystem = 1)", con);

                //SqlDataAdapter da = new SqlDataAdapter(cmd);

                int v_iResult;

                v_iResult = Convert.ToInt32(cmd.ExecuteScalar().ToString());

                if (v_iResult == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {

                return false;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }
    }
}
